const db = require('../db/index')
const bcrypt = require('bcryptjs')
const sequelize = require('sequelize')
const {Op} = require("sequelize")
//引入模型
const initModels = require('../models/init-models')
const crypto = require("crypto");
const moment = require("moment/moment");
const Company = initModels(db).company_info
const Energy = initModels(db).energy_company
const Apartment = initModels(db).apartment_energy
const Pie = initModels(db).apartment_pie
const Station = initModels(db).station_info

//公司列表
exports.getCompany = async (req, res) => {
  try {
    await db.sync()
    let result = await Company.findAll({
      attributes: ['name'],
      group: 'name'
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//能源总览
exports.getOverview = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {year}
    if (timeLabel === 'month') {
      where.month = month
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('createElectricity')), 'createElectricity'],
        [sequelize.fn('SUM', sequelize.col('useElectricity')), 'useElectricity'],
        [sequelize.fn('SUM', sequelize.col('carbon')), 'carbon']
      ],
      where
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//发电/用电量及碳减排量趋势图
exports.getTrend = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {}
    let groupName = 'year'
    if (timeLabel === 'month') {
      groupName = 'month'
      where.year = year
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('createElectricity')), 'createElectricity'],
        [sequelize.fn('SUM', sequelize.col('useElectricity')), 'useElectricity'],
        [sequelize.fn('SUM', sequelize.col('carbon')), 'carbon'],
        [groupName, 'time']
      ],
      where,
      group: groupName,
      order: [
        // [sequelize.fn('CAST', sequelize.col(groupName), 'SIGNED'), 'DESC']
        [sequelize.literal(`${groupName} + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//用水量统计趋势
exports.getWaterTrend = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {}
    let groupName = 'year'
    if (timeLabel === 'month') {
      groupName = 'month'
      where.year = year
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let yearWater = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('water')), 'yearWater']],
      where: {year}
    })
    let monthWater = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('water')), 'monthWater']],
      where: {year, month}
    })
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('water')), 'value'],
        [groupName, 'name']
      ],
      where,
      group: groupName,
      order: [
        // [sequelize.fn('CAST', sequelize.col(groupName), 'SIGNED'), 'DESC']
        [sequelize.literal(`${groupName} + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, {yearWater, monthWater, list: result})
  } catch (error) {
    res.cc(error)
  }
}
//地图
exports.getMap = async (req, res) => {
  try {
    await db.sync()
    let result1 = await Station.findAll({
      attributes: ['fault', 'power', 'total', 'normal', ['location', 'value']],
    })
    let result2 = await Company.findAll({
      attributes: ['name', 'info', ['location', 'value']],
      group: ['name', 'info', 'location']
    })
    res.dataSend('查询成功！', 0, [result1, result2])
  } catch (error) {
    res.cc(error)
  }
}
//充电次数统计趋势
exports.getPower = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {}
    let groupName = 'year'
    if (timeLabel === 'month') {
      groupName = 'month'
      where.year = year
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let yearCharge = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('charge')), 'yearCharge']],
      where: {year}
    })
    let monthCharge = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('charge')), 'monthCharge']],
      where: {year, month}
    })
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('charge')), 'value'],
        [groupName, 'name']
      ],
      where,
      group: groupName,
      order: [
        // [sequelize.fn('CAST', sequelize.col(groupName), 'SIGNED'), 'DESC']
        [sequelize.literal(`${groupName} + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, {yearCharge, monthCharge, list: result})
  } catch (error) {
    res.cc(error)
  }
}
//充电总量统计趋势
exports.getPowerTotal = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {}
    let groupName = 'year'
    if (timeLabel === 'month') {
      groupName = 'month'
      where.year = year
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let yearChargeCapacity = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('chargeCapacity')), 'yearChargeCapacity']],
      where: {year}
    })
    let monthChargeCapacity = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('chargeCapacity')), 'monthChargeCapacity']],
      where: {year, month}
    })
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('chargeCapacity')), 'value'],
        [groupName, 'name']
      ],
      where,
      group: groupName,
      order: [
        // [sequelize.fn('CAST', sequelize.col(groupName), 'SIGNED'), 'DESC']
        [sequelize.literal(`${groupName} + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, {yearChargeCapacity, monthChargeCapacity, list: result})
  } catch (error) {
    res.cc(error)
  }
}
//燃气量统计趋势
exports.getGas = async (req, res) => {
  //接收表单数据
  const {month, year, timeLabel, company} = req.body
  try {
    await db.sync()
    const where = {}
    let groupName = 'year'
    if (timeLabel === 'month') {
      groupName = 'month'
      where.year = year
    }
    if (company !== null && company !== '' && company !== undefined) {
      where.company = company
    }
    let yearGas = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('gas')), 'yearGas']],
      where: {year}
    })
    let monthGas = await Energy.findOne({
      attributes: [[sequelize.fn('SUM', sequelize.col('gas')), 'monthGas']],
      where: {year, month}
    })
    let result = await Energy.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('gas')), 'value'],
        [groupName, 'name']
      ],
      where,
      group: groupName,
      order: [
        // [sequelize.fn('CAST', sequelize.col(groupName), 'SIGNED'), 'DESC']
        [sequelize.literal(`${groupName} + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, {yearGas, monthGas, list: result})
  } catch (error) {
    res.cc(error)
  }
}
//公司数据
exports.getCompanyData = async (req, res) => {
  //接收表单数据
  const {year, company} = req.body
  try {
    await db.sync()
    let result = await Company.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('moneyTotal')), 'moneyTotal'],
        [sequelize.fn('SUM', sequelize.col('income')), 'income'],
        [sequelize.fn('SUM', sequelize.col('taxRevenue')), 'taxRevenue'],
        [sequelize.fn('SUM', sequelize.col('personTotal')), 'personTotal'],
        [sequelize.fn('SUM', sequelize.col('cost')), 'cost'],
        [sequelize.fn('SUM', sequelize.col('debt')), 'debt'],
        [sequelize.fn('SUM', sequelize.col('profit')), 'profit'],
      ],
      where: {year, name: company},
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//小区列表
exports.getApartment = async (req, res) => {
  try {
    await db.sync()
    let result = await Apartment.findAll({
      attributes: ['apartment'],
      group: 'apartment'
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//能耗总览
exports.getConsumptionOverview = async (req, res) => {
  try {
    await db.sync()
    const {apartment, year} = req.body
    let result = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
      ],
      where: {apartment, year}
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//年度能源计划
exports.getPlan = async (req, res) => {
  try {
    await db.sync()
    const {apartment, year} = req.body
    let result = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
        [sequelize.fn('SUM', sequelize.col('estimateElectricity')), 'estimateElectricity'],
        [sequelize.fn('SUM', sequelize.col('estimateWater')), 'estimateWater'],
        [sequelize.fn('SUM', sequelize.col('estimateGas')), 'estimateGas'],
      ],
      where: {apartment, year}
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//本月能耗排行
exports.getOrder = async (req, res) => {
  try {
    await db.sync()
    const {apartment, year, orderChoose} = req.body
    let result = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
        ['floor', 'name']
      ],
      where: {apartment, year},
      group: 'floor',
      order: [[orderChoose, 'ASC']]
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//每月能耗总览
exports.getMonthEnergy = async (req, res) => {
  try {
    await db.sync()
    const {apartment, year} = req.body
    let result = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
        ['month', 'name']
      ],
      where: {apartment, year},
      group: 'month',
      order: [
        [sequelize.literal(`month + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, result)
  } catch (error) {
    res.cc(error)
  }
}
//各能耗月对比
exports.getMonthCompare = async (req, res) => {
  try {
    await db.sync()
    const {apartment, year1, year2} = req.body
    let result1 = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
        ['month', 'name']
      ],
      where: {apartment, year: year1},
      group: 'month',
      order: [
        [sequelize.literal(`month + 0`), 'ASC']
      ]
    })
    let result2 = await Apartment.findAll({
      attributes: [
        [sequelize.fn('SUM', sequelize.col('electricity')), 'electricity'],
        [sequelize.fn('SUM', sequelize.col('water')), 'water'],
        [sequelize.fn('SUM', sequelize.col('gas')), 'gas'],
        ['month', 'name']
      ],
      where: {apartment, year: year2},
      group: 'month',
      order: [
        [sequelize.literal(`month + 0`), 'ASC']
      ]
    })
    res.dataSend('查询成功！', 0, {result1, result2})
  } catch (error) {
    res.cc(error)
  }
}
//月度能耗分析
exports.getAnalysis = async (req, res) => {
  try {
    await db.sync()
    const {apartment} = req.body
    let result1 = await Pie.findAll({
      attributes:[
        [sequelize.fn('SUM', sequelize.col('jkyd')), 'jkyd'],
        [sequelize.fn('SUM', sequelize.col('ktyd')), 'ktyd'],
        [sequelize.fn('SUM', sequelize.col('gqzm')), 'gqzm'],
        [sequelize.fn('SUM', sequelize.col('dtgd')), 'dtgd'],
        [sequelize.fn('SUM', sequelize.col('jtzm')), 'jtzm'],
        [sequelize.fn('SUM', sequelize.col('jdyd')), 'jdyd'],
        [sequelize.fn('SUM', sequelize.col('gsyd')), 'gsyd'],
        [sequelize.fn('SUM', sequelize.col('gqsh')), 'gqsh'],
        [sequelize.fn('SUM', sequelize.col('jtsh')), 'jtsh'],
        [sequelize.fn('SUM', sequelize.col('yys')), 'yys'],
        [sequelize.fn('SUM', sequelize.col('jtyq')), 'jtyq'],
        [sequelize.fn('SUM', sequelize.col('lsryq')), 'lsryq'],
      ],
      where: {apartment},
    })
    let result2 = await Pie.findAll({
      attributes: ['electricity','electricityTB','electricityHB','water','waterTB','waterHB','gas','gasTB','gasHB','floor'],
      where: {apartment}
    })
    res.dataSend('查询成功！', 0, {result1,result2})
  } catch (error) {
    res.cc(error)
  }
}
